﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Text;
using System.Globalization;
using LumenWorks.Framework.IO.Csv;

namespace Dala.Samples.Sql_Script_Generate_From_CSV
{
    public partial class sql_script_generate_from_csv : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
            }
        }
        protected void UploadCsvDataToDatabase(object sender, EventArgs e)
        {
            try
            {
                UploadCsvFile();
            }
            catch (Exception ex)
            {
                ShowErrorMsg("success", "Error! " + ex.Message);

            }
        }
        private void UploadCsvFile()
        {
            string physicalTempFilePath = string.Empty;
            FileUpload inputFile = flucsv;
            if (inputFile.HasFile)
            {
                string contentType = inputFile.PostedFile.ContentType.ToLower();
                var Extension = inputFile.FileName.Substring(inputFile.FileName.IndexOf('.') + 1).ToLower();
                if (Extension == "csv")
                {
                    physicalTempFilePath = DeleteAndSaveCsvFile(physicalTempFilePath, inputFile, contentType);
                    CsvReader csv = new CsvReader(new StreamReader(physicalTempFilePath), false); // false = no header is allowed in csv , true = header is allowed in csv file
                    csv.MissingFieldAction = MissingFieldAction.ReplaceByNull;
                    StringBuilder idBuilder = new StringBuilder();
                    while (csv.ReadNextRecord())
                    {
                        string emp = csv[0];
                        if (!string.IsNullOrEmpty(emp))
                        {
                            idBuilder.Append("Update [ActivationCode] Set [IsUsed]='True',[UploadDate]='2/23/2013' Where ActivationCodeName='" + emp.ToString(CultureInfo.InvariantCulture) + "'");
                            idBuilder.AppendLine();
                        }
                    }
                    csv.Dispose();
                    StreamWriter xceptionalWriter = new StreamWriter(Server.MapPath("~/Sql-Script-Generate-From-CSV/csv-upload/SQL_Script_Generated.sql"), false);
                    xceptionalWriter.WriteLine(idBuilder.ToString());
                    xceptionalWriter.Flush();
                    xceptionalWriter.Close();
                    ShowErrorMsg("success", "Data Uploaded Successfully !");
                }
                else
                {
                    ShowErrorMsg("error", "Please select a valid CSV file!!");
                }
            }
        }
        private string DeleteAndSaveCsvFile(string physicalTempFilePath, FileUpload inputFile, string contentType)
        {
            if (contentType == "application/vnd.ms-excel"
                || contentType == "application/ms-excel"
                || contentType == "application/x-zip-compressed"
                || contentType == "application/octet-stream"
                || contentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
                || contentType == "text/csv")
            {
                physicalTempFilePath = "~/Sql-Script-Generate-From-CSV/csv-upload/" + Path.GetFileName(inputFile.FileName);
                physicalTempFilePath = Server.MapPath(physicalTempFilePath);

                if (File.Exists(physicalTempFilePath))
                {
                    File.Delete(physicalTempFilePath);
                }
                inputFile.SaveAs(physicalTempFilePath);
            }
            return physicalTempFilePath;
        }

        private void ShowErrorMsg(string _class, string _msg)
        {
            divMessage.Style.Value = "display:block;";
            divMessage.Attributes.Add("class", _class);
            divMessage.InnerHtml = _msg;
        }
    }
}